<!-- TOC -->

- [2.1. Введение](#21-%d0%92%d0%b2%d0%b5%d0%b4%d0%b5%d0%bd%d0%b8%d0%b5)
- [2.2. Основные понятия](#22-%d0%9e%d1%81%d0%bd%d0%be%d0%b2%d0%bd%d1%8b%d0%b5-%d0%bf%d0%be%d0%bd%d1%8f%d1%82%d0%b8%d1%8f)
- [2.3. Создание таблицы](#23-%d0%a1%d0%be%d0%b7%d0%b4%d0%b0%d0%bd%d0%b8%d0%b5-%d1%82%d0%b0%d0%b1%d0%bb%d0%b8%d1%86%d1%8b)
- [2.4. Добавление строк в таблицу](#24-%d0%94%d0%be%d0%b1%d0%b0%d0%b2%d0%bb%d0%b5%d0%bd%d0%b8%d0%b5-%d1%81%d1%82%d1%80%d0%be%d0%ba-%d0%b2-%d1%82%d0%b0%d0%b1%d0%bb%d0%b8%d1%86%d1%83)
- [2.5. Выполнение запроса](#25-%d0%92%d1%8b%d0%bf%d0%be%d0%bb%d0%bd%d0%b5%d0%bd%d0%b8%d0%b5-%d0%b7%d0%b0%d0%bf%d1%80%d0%be%d1%81%d0%b0)
- [2.6. Соединения таблиц](#26-%d0%a1%d0%be%d0%b5%d0%b4%d0%b8%d0%bd%d0%b5%d0%bd%d0%b8%d1%8f-%d1%82%d0%b0%d0%b1%d0%bb%d0%b8%d1%86)
- [2.7. Агрегатные функции](#27-%d0%90%d0%b3%d1%80%d0%b5%d0%b3%d0%b0%d1%82%d0%bd%d1%8b%d0%b5-%d1%84%d1%83%d0%bd%d0%ba%d1%86%d0%b8%d0%b8)
- [2.8. Изменение данных](#28-%d0%98%d0%b7%d0%bc%d0%b5%d0%bd%d0%b5%d0%bd%d0%b8%d0%b5-%d0%b4%d0%b0%d0%bd%d0%bd%d1%8b%d1%85)
- [2.9. Удаление данных](#29-%d0%a3%d0%b4%d0%b0%d0%bb%d0%b5%d0%bd%d0%b8%d0%b5-%d0%b4%d0%b0%d0%bd%d0%bd%d1%8b%d1%85)

<!-- /TOC -->

# [2.1. Введение](https://postgrespro.ru/docs/postgrespro/12/tutorial-sql-intro)

В этой главе рассматривается использование SQL для выполнения простых операций. Она призвана только познакомить вас с SQL, но ни в коей мере не претендует на исчерпывающее руководство. Про SQL написано множество книг, включая [melt93] и [date97]. При этом следует учитывать, что некоторые возможности языка Postgres Pro являются расширениями стандарта.

В следующих примерах мы предполагаем, что вы создали базу данных mydb, как описано в предыдущей главе, и смогли запустить psql.

Примеры этого руководства также можно найти в пакете исходного кода Postgres Pro в каталоге src/tutorial/. (Двоичные дистрибутивы Postgres Pro могут не включать эти файлы.) Чтобы использовать эти файлы, перейдите в этот каталог и запустите make:

    $ cd ..../src/tutorial
    $ make
При этом будут созданы скрипты и скомпилированы модули C, содержащие пользовательские функции и типы. Затем, чтобы начать работу с учебным материалом, выполните следующее:

    $ cd ..../tutorial
    $ psql -s mydb

    ...


    mydb=> \i basics.sql
Команда \i считывает и выполняет команды из заданного файла. Переданный psql параметр -s переводит его в пошаговый режим, когда он делает паузу перед отправкой каждого оператора серверу. Команды, используемые в этом разделе, содержатся в файле basics.sql.



# [2.2. Основные понятия](https://postgrespro.ru/docs/postgrespro/12/tutorial-concepts)

Postgres Pro — это реляционная система управления базами данных (РСУБД). Это означает, что это система управления данными, представленными в виде отношений (relation). Отношение — это математически точное обозначение таблицы. Хранение данных в таблицах так распространено сегодня, что это кажется самым очевидным вариантом, хотя есть множество других способов организации баз данных. Например, файлы и каталоги в Unix-подобных операционных системах образуют иерархическую базу данных, а сегодня активно развиваются объектно-ориентированные базы данных.

Любая таблица представляет собой именованный набор строк. Все строки таблицы имеют одинаковый набор именованных столбцов, при этом каждому столбцу назначается определённый тип данных. Хотя порядок столбцов во всех строках фиксирован, важно помнить, что SQL не гарантирует какой-либо порядок строк в таблице (хотя их можно явно отсортировать при выводе).

Таблицы объединяются в базы данных, а набор баз данных, управляемый одним экземпляром сервера Postgres Pro, образует кластер баз данных.

# [2.3. Создание таблицы](https://postgrespro.ru/docs/postgrespro/12/tutorial-table)

Вы можете создать таблицу, указав её имя и перечислив все имена столбцов и их типы:

    CREATE TABLE weather (
        city            varchar(80),
        temp_lo         int,           -- минимальная температура дня
        temp_hi         int,           -- максимальная температура дня
        prcp            real,          -- уровень осадков
        date            date
    );
Весь этот текст можно ввести в psql вместе с символами перевода строк. psql понимает, что команда продолжается до точки с запятой.

В командах SQL можно свободно использовать пробельные символы (пробелы, табуляции и переводы строк). Это значит, что вы можете ввести команду, выровняв её по-другому или даже уместив в одной строке. Два минуса («--») обозначают начало комментария. Всё, что идёт за ними до конца строки, игнорируется. SQL не чувствителен к регистру в ключевых словах и идентификаторах, за исключением идентификаторов, взятых в кавычки (в данном случае это не так).

varchar(80) определяет тип данных, допускающий хранение произвольных символьных строк длиной до 80 символов. int — обычный целочисленный тип. real — тип для хранения чисел с плавающей точкой одинарной точности. date — тип даты. (Да, столбец типа date также называется date. Это может быть удобно или вводить в заблуждение — как посмотреть.)

Postgres Pro поддерживает стандартные типы SQL: int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp и interval, а также другие универсальные типы и богатый набор геометрических типов. Кроме того, Postgres Pro можно расширять, создавая набор собственных типов данных. Как следствие, имена типов не являются ключевыми словами в данной записи, кроме тех случаев, когда это требуется для реализации особых конструкций стандарта SQL.

Во втором примере мы сохраним в таблице города и их географическое положение:

    CREATE TABLE cities (
        name            varchar(80),
        location        point
    );
Здесь point — пример специфического типа данных Postgres Pro.

Наконец, следует сказать, что если вам больше не нужна какая-либо таблица, или вы хотите пересоздать её по-другому, вы можете удалить её, используя следующую команду:

    DROP TABLE имя_таблицы;

# [2.4. Добавление строк в таблицу](https://postgrespro.ru/docs/postgrespro/12/tutorial-populate)

Для добавления строк в таблицу используется оператор INSERT:

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
Заметьте, что для всех типов данных применяются довольно очевидные форматы. Константы, за исключением простых числовых значений, обычно заключаются в апострофы ('), как в данном примере. Тип date на самом деле очень гибок и принимает разные форматы, но в данном введении мы будем придерживаться простого и однозначного.

Тип point требует ввода пары координат, например таким образом:

    INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
Показанный здесь синтаксис требует, чтобы вы запомнили порядок столбцов. Можно также применить альтернативную запись, перечислив столбцы явно:

    INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
        VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
Вы можете перечислить столбцы в другом порядке, если желаете опустить некоторые из них, например, если уровень осадков (столбец prcp) неизвестен:

    INSERT INTO weather (date, city, temp_hi, temp_lo)
        VALUES ('1994-11-29', 'Hayward', 54, 37);
Многие разработчики предпочитают явно перечислять столбцы, а не полагаться на их порядок в таблице.

Пожалуйста, введите все показанные выше команды, чтобы у вас были данные, с которыми можно будет работать дальше.

Вы также можете загрузить большой объём данных из обычных текстовых файлов, применив команду COPY. Обычно это будет быстрее, так как команда COPY оптимизирована для такого применения, хотя и менее гибка, чем INSERT. Например, её можно использовать так:

    COPY weather FROM '/home/user/weather.txt';
здесь подразумевается, что данный файл доступен на компьютере, где работает серверный процесс, а не на клиенте, так как указанный файл будет прочитан непосредственно на сервере. Подробнее об этом вы можете узнать в описании команды COPY.

# [2.5. Выполнение запроса](https://postgrespro.ru/docs/postgrespro/12/tutorial-select)

Чтобы получить данные из таблицы, нужно выполнить запрос. Для этого предназначен SQL-оператор SELECT. Он состоит из нескольких частей: выборки (в которой перечисляются столбцы, которые должны быть получены), списка таблиц (в нём перечисляются таблицы, из которых будут получены данные) и необязательного условия (определяющего ограничения). Например, чтобы получить все строки таблицы weather, введите:

    SELECT * FROM weather;
Здесь * — это краткое обозначение «всех столбцов». [2] Таким образом, это равносильно записи:

    SELECT city, temp_lo, temp_hi, prcp, date FROM weather;
В результате должно получиться:

        city      | temp_lo | temp_hi | prcp |    date
    ---------------+---------+---------+------+------------
    San Francisco |      46 |      50 | 0.25 | 1994-11-27
    San Francisco |      43 |      57 |    0 | 1994-11-29
    Hayward       |      37 |      54 |      | 1994-11-29
    (3 rows)
В списке выборки вы можете писать не только ссылки на столбцы, но и выражения. Например, вы можете написать:

    SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
И получить в результате:

        city      | temp_avg |    date
    ---------------+----------+------------
    San Francisco |       48 | 1994-11-27
    San Francisco |       50 | 1994-11-29
    Hayward       |       45 | 1994-11-29
    (3 rows)
Обратите внимание, как предложение AS позволяет переименовать выходной столбец. (Само слово AS можно опускать.)

Запрос можно дополнить «условием», добавив предложение WHERE, ограничивающее множество возвращаемых строк. В предложении WHERE указывается логическое выражение (проверка истинности), которое служит фильтром строк: в результате оказываются только те строки, для которых это выражение истинно. В этом выражении могут присутствовать обычные логические операторы (AND, OR и NOT). Например, следующий запрос покажет, какая погода была в Сан-Франциско в дождливые дни:

    SELECT * FROM weather
        WHERE city = 'San Francisco' AND prcp > 0.0;
Результат:

        city      | temp_lo | temp_hi | prcp |    date
    ---------------+---------+---------+------+------------
    San Francisco |      46 |      50 | 0.25 | 1994-11-27
    (1 row)
Вы можете получить результаты запроса в определённом порядке:

    SELECT * FROM weather
        ORDER BY city;
        city      | temp_lo | temp_hi | prcp |    date
    ---------------+---------+---------+------+------------
    Hayward       |      37 |      54 |      | 1994-11-29
    San Francisco |      43 |      57 |    0 | 1994-11-29
    San Francisco |      46 |      50 | 0.25 | 1994-11-27
В этом примере порядок сортировки определён не полностью, поэтому вы можете получить строки Сан-Франциско в любом порядке. Но вы всегда получите результат, показанный выше, если напишете:

    SELECT * FROM weather
        ORDER BY city, temp_lo;
Если требуется, вы можете убрать дублирующиеся строки из результата запроса:

    SELECT DISTINCT city
        FROM weather;
        city
    ---------------
    Hayward
    San Francisco
    (2 rows)
И здесь порядок строк также может варьироваться. Чтобы получать неизменные результаты, соедините предложения DISTINCT и ORDER BY: [3]

    SELECT DISTINCT city
        FROM weather
        ORDER BY city;

[2] Хотя запросы SELECT * часто пишут экспромтом, это считается плохим стилем в производственном коде, так как результат таких запросов будет меняться при добавлении новых столбцов.

[3] В некоторых СУБД, включая старые версии Postgres Pro, реализация предложения DISTINCT автоматически упорядочивает строки, так что ORDER BY добавлять не обязательно. Но стандарт SQL этого не требует и текущая версия Postgres Pro не гарантирует определённого порядка строк после DISTINCT.

# [2.6. Соединения таблиц](https://postgrespro.ru/docs/postgrespro/12/tutorial-join)

До этого все наши запросы обращались только к одной таблице. Однако запросы могут также обращаться сразу к нескольким таблицам или обращаться к той же таблице так, что одновременно будут обрабатываться разные наборы её строк. Запрос, обращающийся к разным наборам строк одной или нескольких таблиц, называется соединением (JOIN). Например, мы захотели перечислить все погодные события вместе с координатами соответствующих городов. Для этого мы должны сравнить столбец city каждой строки таблицы weather со столбцом name всех строк таблицы cities и выбрать пары строк, для которых эти значения совпадают.

Примечание
Это не совсем точная модель. Обычно соединения выполняются эффективнее (сравниваются не все возможные пары строк), но это скрыто от пользователя.

Это можно сделать с помощью следующего запроса:

    SELECT *
        FROM weather, cities
        WHERE city = name;
        city     |temp_lo|temp_hi| prcp|    date   |     name     | location
    --------------+-------+-------+-----+-----------+--------------+----------
    San Francisco|     46|     50| 0.25| 1994-11-27| San Francisco| (-194,53)
    San Francisco|     43|     57|    0| 1994-11-29| San Francisco| (-194,53)
    (2 rows)
Обратите внимание на две особенности полученных данных:

В результате нет строки с городом Хейуорд (Hayward). Так получилось потому, что в таблице cities нет строки для данного города, а при соединении все строки таблицы weather, для которых не нашлось соответствие, опускаются. Вскоре мы увидим, как это можно исправить.

Название города оказалось в двух столбцах. Это правильно и объясняется тем, что столбцы таблиц weather и cities были объединены. Хотя на практике это нежелательно, поэтому лучше перечислить нужные столбцы явно, а не использовать *:

    SELECT city, temp_lo, temp_hi, prcp, date, location
        FROM weather, cities
        WHERE city = name;
Упражнение: Попробуйте определить, что будет делать этот запрос без предложения WHERE.

Так как все столбцы имеют разные имена, анализатор запроса автоматически понимает, к какой таблице они относятся. Если бы имена столбцов в двух таблицах повторялись, вам пришлось бы дополнить имена столбцов, конкретизируя, что именно вы имели в виду:

    SELECT weather.city, weather.temp_lo, weather.temp_hi,
        weather.prcp, weather.date, cities.location
        FROM weather, cities
        WHERE cities.name = weather.city;
Вообще хорошим стилем считается указывать полные имена столбцов в запросе соединения, чтобы запрос не поломался, если позже в таблицы будут добавлены столбцы с повторяющимися именами.

Запросы соединения, которые вы видели до этого, можно также записать в другом виде:

    SELECT *
        FROM weather INNER JOIN cities ON (weather.city = cities.name);
Эта запись не так распространена, как первый вариант, но мы показываем её, чтобы вам было проще понять следующие темы.

Сейчас мы выясним, как вернуть записи о погоде в городе Хейуорд. Мы хотим, чтобы запрос просканировал таблицу weather и для каждой её строки нашёл соответствующую строку в таблице cities. Если же такая строка не будет найдена, мы хотим, чтобы вместо значений столбцов из таблицы cities были подставлены «пустые значения». Запросы такого типа называются внешними соединениями. (Соединения, которые мы видели до этого, называются внутренними.) Эта команда будет выглядеть так:

    SELECT *
        FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);

        city     |temp_lo|temp_hi| prcp|    date   |     name     | location
    --------------+-------+-------+-----+-----------+--------------+----------
    Hayward      |     37|     54|     | 1994-11-29|              |
    San Francisco|     46|     50| 0.25| 1994-11-27| San Francisco| (-194,53)
    San Francisco|     43|     57|    0| 1994-11-29| San Francisco| (-194,53)
    (3 rows)
Этот запрос называется левым внешним соединением, потому что из таблицы в левой части оператора будут выбраны все строки, а из таблицы справа только те, которые удалось сопоставить каким-нибудь строкам из левой. При выводе строк левой таблицы, для которых не удалось найти соответствия в правой, вместо столбцов правой таблицы подставляются пустые значения (NULL).

Упражнение: Существуют также правые внешние соединения и полные внешние соединения. Попробуйте выяснить, что они собой представляют.

В соединении мы также можем замкнуть таблицу на себя. Это называется замкнутым соединением. Например, представьте, что мы хотим найти все записи погоды, в которых температура лежит в диапазоне температур других записей. Для этого мы должны сравнить столбцы temp_lo и temp_hi каждой строки таблицы weather со столбцами temp_lo и temp_hi другого набора строк weather. Это можно сделать с помощью следующего запроса:

    SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
        W2.city, W2.temp_lo AS low, W2.temp_hi AS high
        FROM weather W1, weather W2
        WHERE W1.temp_lo < W2.temp_lo
        AND W1.temp_hi > W2.temp_hi;

        city      | low | high |     city      | low | high
    ---------------+-----+------+---------------+-----+------
    San Francisco |  43 |   57 | San Francisco |  46 |   50
    Hayward       |  37 |   54 | San Francisco |  46 |   50
    (2 rows)
Здесь мы ввели новые обозначения таблицы weather: W1 и W2, чтобы можно было различить левую и правую стороны соединения. Вы можете использовать подобные псевдонимы и в других запросах для сокращения:

    SELECT *
        FROM weather w, cities c
        WHERE w.city = c.name;
Вы будете встречать сокращения такого рода довольно часто.

# [2.7. Агрегатные функции](https://postgrespro.ru/docs/postgrespro/12/tutorial-agg)

Как большинство других серверов реляционных баз данных, Postgres Pro поддерживает агрегатные функции. Агрегатная функция вычисляет единственное значение, обрабатывая множество строк. Например, есть агрегатные функции, вычисляющие: count (количество), sum (сумму), avg (среднее), max (максимум) и min (минимум) для набора строк.

К примеру, мы можем найти самую высокую из всех минимальных дневных температур:

    SELECT max(temp_lo) FROM weather;
    max
    -----
    46
    (1 row)
Если мы хотим узнать, в каком городе (или городах) наблюдалась эта температура, можно попробовать:

    SELECT city FROM weather WHERE temp_lo = max(temp_lo);     НЕВЕРНО
но это не будет работать, так как агрегатную функцию max нельзя использовать в предложении WHERE. (Это ограничение объясняется тем, что предложение WHERE должно определить, для каких строк вычислять агрегатную функцию, так что оно, очевидно, должно вычисляться до агрегатных функций.) Однако, как часто бывает, запрос можно перезапустить и получить желаемый результат, применив подзапрос:

    SELECT city FROM weather
        WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
        city
    ---------------
    San Francisco
    (1 row)
Теперь всё в порядке — подзапрос выполняется отдельно и результат агрегатной функции вычисляется вне зависимости от того, что происходит во внешнем запросе.

Агрегатные функции также очень полезны в сочетании с предложением GROUP BY. Например, мы можем получить максимум минимальной дневной температуры в разрезе городов:

    SELECT city, max(temp_lo)
        FROM weather
        GROUP BY city;
        city      | max
    ---------------+-----
    Hayward       |  37
    San Francisco |  46
    (2 rows)
Здесь мы получаем по одной строке для каждого города. Каждый агрегатный результат вычисляется по строкам таблицы, соответствующим отдельному городу. Мы можем отфильтровать сгруппированные строки с помощью предложения HAVING:

    SELECT city, max(temp_lo)
        FROM weather
        GROUP BY city
        HAVING max(temp_lo) < 40;
    city   | max
    ---------+-----
    Hayward |  37
    (1 row)
Мы получаем те же результаты, но только для тех городов, где все значения temp_lo меньше 40. Наконец, если нас интересуют только города, названия которых начинаются с «S», мы можем сделать:

    SELECT city, max(temp_lo)
        FROM weather
        WHERE city LIKE 'S%'            -- (1)
        GROUP BY city
        HAVING max(temp_lo) < 40;
    (1)

Оператор LIKE (выполняющий сравнение по шаблону) рассматривается в Разделе 9.7.

Важно понимать, как соотносятся агрегатные функции и SQL-предложения WHERE и HAVING. Основное отличие WHERE от HAVING заключается в том, что WHERE сначала выбирает строки, а затем группирует их и вычисляет агрегатные функции (таким образом, она отбирает строки для вычисления агрегатов), тогда как HAVING отбирает строки групп после группировки и вычисления агрегатных функций. Как следствие, предложение WHERE не должно содержать агрегатных функций; не имеет смысла использовать агрегатные функции для определения строк для вычисления агрегатных функций. Предложение HAVING, напротив, всегда содержит агрегатные функции. (Строго говоря, вы можете написать предложение HAVING, не используя агрегаты, но это редко бывает полезно. То же самое условие может работать более эффективно на стадии WHERE.)

В предыдущем примере мы смогли применить фильтр по названию города в предложении WHERE, так как названия не нужно агрегировать. Такой фильтр эффективнее, чем дополнительное ограничение HAVING, потому что с ним не приходится группировать и вычислять агрегаты для всех строк, не удовлетворяющих условию WHERE.

# [2.8. Изменение данных](https://postgrespro.ru/docs/postgrespro/12/tutorial-update)

Данные в существующих строках можно изменять, используя команду UPDATE. Например, предположим, что вы обнаружили, что все значения температуры после 28 ноября завышены на два градуса. Вы можете поправить ваши данные следующим образом:

    UPDATE weather
        SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
        WHERE date > '1994-11-28';
Посмотрите на новое состояние данных:

SELECT * FROM weather;

        city      | temp_lo | temp_hi | prcp |    date
    ---------------+---------+---------+------+------------
    San Francisco |      46 |      50 | 0.25 | 1994-11-27
    San Francisco |      41 |      55 |    0 | 1994-11-29
    Hayward       |      35 |      52 |      | 1994-11-29
    (3 rows)

# [2.9. Удаление данных](https://postgrespro.ru/docs/postgrespro/12/tutorial-delete)

Строки также можно удалить из таблицы, используя команду DELETE. Предположим, что вас больше не интересует погода в Хейуорде. В этом случае вы можете удалить ненужные строки из таблицы:

DELETE FROM weather WHERE city = 'Hayward';
Записи всех наблюдений, относящиеся к Хейуорду, удалены.

    SELECT * FROM weather;
        city      | temp_lo | temp_hi | prcp |    date
    ---------------+---------+---------+------+------------
    San Francisco |      46 |      50 | 0.25 | 1994-11-27
    San Francisco |      41 |      55 |    0 | 1994-11-29
    (2 rows)
Остерегайтесь операторов вида

    DELETE FROM имя_таблицы;
Без указания условия DELETE удалит все строки данной таблицы, полностью очистит её. При этом система не попросит вас подтвердить операцию!
